package com.testjpa.dao;

import com.testjpa.entity.MyUser;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;
/**
 * 作者: LCG
 * 日期: 2020/5/25 11:20
 * 描述: 注解的方式进行查询
 */
public interface MyQueryDao extends JpaRepository<MyUser,Long> {


    /**
     * query查询 参数传递的方式1
     * @param userName
     * @return
     */
    @Query(value = "select * from jpa_user where user_name like CONCAT('%',:userName,'%')",nativeQuery = true)
    public List<MyUser> getUserList(@Param("userName") String userName);


    /**
     * query查询 参数传递的方式2
     * @param userName
     * @return
     */
    @Query(value = "select * from jpa_user where user_name like CONCAT('%',?1,'%')",nativeQuery = true)
    public List<MyUser> getUserList2(String userName);

    /**
     * Query 分页
     * @param userName
     * @param pageable
     * @return
     */
    @Query(value = "select * from jpa_user where user_name like CONCAT('%',?1,'%')",nativeQuery = true)
    public Page<MyUser> getUserList22(String userName, Pageable pageable);


    /**
     * 执行一些特殊的查询比如 返回一个 Map
     * @param id
     * @return
     */
    @Query(value = "select ju.user_name,jd.depName from jpa_user ju LEFT JOIN jpa_dep jd on ju.dep_id=jd.id where ju.id=?1",nativeQuery = true)
    public List<Map<String,Object>> getUserAndDepList(Long id);



    /**
     *
     * @param id
     * @return
     */
    @Query(value = "select ju.user_name,jd.depName from jpa_user ju LEFT JOIN jpa_dep jd on ju.dep_id=jd.id where ju.id=?1",nativeQuery = true)
    public List<KuoZhan> getUserAndDepList2(Long id);


}
